﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Newtonsoft.Json;
using Meeting.DBUtility;
using System.Data;
using Meeting.Model;
using Newtonsoft.Json.Converters;
using System.Data.SqlClient;
using System.IO;

namespace Meeting.BLL
{
    /// <summary>
    /// 模块作者：许文亭
    /// 模块日期：2013-1-4
    /// 模块功能：会议表Meeting 增删查改
    /// </summary>
    public class MeetingBLL
    {
        public string errorString = "";

        /// <summary>
        /// 获取最新一条会议记录
        /// </summary>
        /// <returns></returns>
        public string GetLastMeeting(int category)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select top 1 m.Id,m.Name,m.StartDate From dbo.Meeting m inner join MeetingCategory mc on m.CategoryId = mc.Id where mc.Name = '{0}' Order By StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, category));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.Message = "获取数据成功";
                    response.ResponseData = ds.Tables[0];
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = "异常" + errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取某一类会议最新的会议信息
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public string GetLastMeetingInfo(int category, string userId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = @"Select distinct top 1 m.Id,m.StartDate,vr.Number as VideoRoomNumber,vr.HostEmail,vr.HostPass,m.HostId,m.Name as MeetingTitle,m.EndDate 
                               From dbo.Meeting m inner join MeetingEmployee me on m.Id = me.MeetingId
                                    left outer join VideoRoom vr on m.VideoRoomId = vr.Id
                               where m.CategoryId = {0} and me.EmployeeId = '{1}' and Status = 2  Order By m.StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, category, userId));
                //
                if (ds == null)//|| ds.Tables[0].Rows.Count == 0
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        response.Message = "没有最新会议";
                        response.ResponseData = null;
                    }
                    else
                    {
                        response.Message = "获取数据成功";
                        //获取数据成功之后，对该用户进行签到
                        DateTime start = Convert.ToDateTime(ds.Tables[0].Rows[0]["StartDate"]);
                        //DateTime end = Convert.ToDateTime(ds.Tables[0].Rows[0]["EndDate"]);
                        if (DateTime.Now > start.AddMinutes(-30)) //&& DateTime.Now < end)
                        {
                            DbHelperSQL.GetSingle(string.Format("update MeetingEmployee set LoginDate = '{1}' where EmployeeId='{0}' and LoginDate is null", userId, DateTime.Now.ToString()));
                        }
                        //response.ResponseData = ds.Tables[0].Rows.Count == 0 ? Guid.Empty : ds.Tables[0].Rows[0][0];
                        response.ResponseData = ds.Tables[0].Rows[0];
                    }
                }

                var obj = JsonConvert.SerializeObject(response);
                return obj;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = "异常" + errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取出去当前会议的最近十次会议（主键，时间）
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public string GetLastTenMeeting(int category, int id)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select top 10 Id,StartDate From dbo.Meeting where Id <> '" + id + "' and CategoryId={0} Order By StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, category));
                //
                if (ds == null)// || ds.Tables[0].Rows.Count == 0
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.Message = "获取数据成功";
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = "异常" + errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取用户参与的会议
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetMyMeeting(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select distinct * From dbo.Meeting m inner join MeetingEmployee me on m.Id = me.MeetingId where me.EmployeeId = '{0}' Order By StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }
        /// <summary>
        /// 历史查询
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public string GetMeetingHistory(string filter)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select distinct m.* From dbo.Meeting m inner join MeetingEmployee me on m.Id = me.MeetingId inner join MeetingTopic mt on m.Id = mt.MeetingId inner join Topic t on mt.TopicId = t.Id where {0} Order By m.StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, filter));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        public string GetMeetingPage(int pageIndex, int pageSize, string filter)
        {
            int nAllRecord = 0, nAllPages = 0;

            DataTable dt = DbHelperSQL.GetPage("m.Id", " m.* ", " dbo.Meeting m inner join MeetingEmployee me on m.Id = me.MeetingId inner join MeetingTopic mt on m.Id = mt.MeetingId inner join Topic t on mt.TopicId = t.Id", filter, " m.StartDate desc ", pageIndex, pageSize, out nAllRecord, out nAllPages);
            JsonResponse response = new JsonResponse();

            if (dt == null)
            {
                response.success = false;
                response.Message = DbHelperSQL.errorString;
            }
            else
            {
                response.success = true;
                response.ResponseData = dt;
                response.DataCount = nAllRecord;
            }

            Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
            timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

            return JsonConvert.SerializeObject(response, timeConverter);
        }


        /// <summary>
        /// 获取会议的议题
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetTopicBy(int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = @"select  mt.*,t.Title as TopicTitle from MeetingTopic mt inner join Topic t on mt.TopicId = t.Id where MeetingId = {0} order by mt.Sort
                               select  * from MeetingSummary where MeetingId = {0} order by UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    Model.Meeting m = new Model.Meeting();

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        m.Topics = new List<Model.MeetingTopic>();
                        Model.MeetingTopic topic;
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            topic = new Model.MeetingTopic();
                            if (dr["TopicId"] != null && dr["TopicId"].ToString() != "")
                            {
                                topic.TopicId = new Guid(dr["TopicId"].ToString());
                            }
                            if (dr["MeetingId"] != null && dr["MeetingId"].ToString() != "")
                            {
                                topic.MeetingId = int.Parse(dr["MeetingId"].ToString());
                            }
                            if (dr["RoleId"] != null && dr["RoleId"].ToString() != "")
                            {
                                topic.RoleId = dr["RoleId"].ToString();
                            }
                            if (dr["EmployeeId"] != null && dr["EmployeeId"].ToString() != "")
                            {
                                topic.EmployeeId = dr["EmployeeId"].ToString();
                            }
                            if (dr["EmployeeName"] != null && dr["EmployeeName"].ToString() != "")
                            {
                                topic.EmployeeName = dr["EmployeeName"].ToString();
                            }
                            if (dr["EmployeeTitle"] != null && dr["EmployeeTitle"].ToString() != "")
                            {
                                topic.EmployeeTitle = dr["EmployeeTitle"].ToString();
                            }
                            if (dr["Sort"] != null && dr["Sort"].ToString() != "")
                            {
                                topic.Sort = int.Parse(dr["Sort"].ToString());
                            }
                            if (dr["TopicTitle"] != null && dr["TopicTitle"].ToString() != "")
                            {
                                topic.TopicTitle = dr["TopicTitle"].ToString();
                            }
                            m.Topics.Add(topic);

                        }
                    }

                    m.Summaries = new List<MeetingSummary>();
                    if (ds.Tables[1].Rows.Count > 0)
                    {
                        Model.MeetingSummary ms;
                        foreach (DataRow dr in ds.Tables[1].Rows)
                        {
                            ms = new MeetingSummary();
                            if (dr["Id"] != null && dr["Id"].ToString() != "")
                            {
                                ms.Id = new Guid(dr["Id"].ToString());
                            }
                            if (dr["MeetingId"] != null && dr["MeetingId"].ToString() != "")
                            {
                                ms.MeetingId = int.Parse(dr["MeetingId"].ToString());
                            }
                            if (dr["Sort"] != null && dr["Sort"].ToString() != "")
                            {
                                ms.Sort = int.Parse(dr["Sort"].ToString());
                            }
                            if (dr["Path"] != null && dr["Path"].ToString() != "")
                            {
                                ms.Path = dr["Path"].ToString();
                            }
                            if (dr["Name"] != null && dr["Name"].ToString() != "")
                            {
                                ms.Name = dr["Name"].ToString();
                            }
                            if (dr["Format"] != null && dr["Format"].ToString() != "")
                            {
                                ms.Format = dr["Format"].ToString();
                            }
                            if (dr["UploadDate"] != null && dr["UploadDate"].ToString() != "")
                            {
                                ms.UploadDate = DateTime.Parse(dr["UploadDate"].ToString());
                            }
                            if (dr["ImageCount"] != null && dr["ImageCount"].ToString() != "")
                            {
                                ms.ImageCount = int.Parse(dr["ImageCount"].ToString());
                            }
                            if (dr["Comment"] != null && dr["Comment"].ToString() != "")
                            {
                                ms.Comment = dr["Comment"].ToString();
                            }
                            if (dr["IsActive"] != null && dr["IsActive"].ToString() != "")
                            {
                                if ((dr["IsActive"].ToString() == "1") || (dr["IsActive"].ToString().ToLower() == "true"))
                                {
                                    ms.IsActive = true;
                                }
                                else
                                {
                                    ms.IsActive = false;
                                }
                            }
                            if (dr["EmployeeId"] != null && dr["EmployeeId"].ToString() != "")
                            {
                                ms.EmployeeId = dr["EmployeeId"].ToString();
                            }
                            if (dr["EmployeeName"] != null && dr["EmployeeName"].ToString() != "")
                            {
                                ms.EmployeeName = dr["EmployeeName"].ToString();
                            }
                            if (dr["EmployeeTitle"] != null && dr["EmployeeTitle"].ToString() != "")
                            {
                                ms.EmployeeTitle = dr["EmployeeTitle"].ToString();
                            }
                            if (dr["RoleId"] != null && dr["RoleId"].ToString() != "")
                            {
                                ms.RoleId = dr["RoleId"].ToString();
                            }
                            if (dr["IsSubmit"] != null && dr["IsSubmit"].ToString() != "")
                            {
                                if ((dr["IsSubmit"].ToString() == "1") || (dr["IsSubmit"].ToString().ToLower() == "true"))
                                {
                                    ms.IsSubmit = true;
                                }
                                else
                                {
                                    ms.IsSubmit = false;
                                }
                            }
                            if (dr["Description"] != null && dr["Description"].ToString() != "")
                            {
                                ms.Description = dr["Description"].ToString();
                            }
                            if (dr["Agent"] != null && dr["Agent"].ToString() != "")
                            {
                                ms.Agent = dr["Agent"].ToString();
                            }
                            if (dr["ExtNumber"] != null && dr["ExtNumber"].ToString() != "")
                            {
                                ms.ExtNumber = dr["ExtNumber"].ToString();
                            }
                            m.Summaries.Add(ms);
                        }
                    }
                    response.ResponseData = m;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }
        public string GetExceptTopicBy(int meetingId, Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select  mt.*,t.Title as TopicTitle from MeetingTopic mt inner join Topic t on mt.TopicId = t.Id where MeetingId = {0} and mt.TopicId <> '{1}' order by mt.Sort";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId, topicId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取议题的相关文档
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetDocBy(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select td.* from MeetingTopicDocument mtd inner join TopicDocument td on mtd.DocumentId = td.Id where mtd.TopicId='{0}' order by td.DocumentCategory,td.Sort,td.UploadDate";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }
        public string GetExceptDocBy(Guid topicId, Guid docId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select * from TopicDocument where TopicId='{0}' and Id<>'{1}' order by DocumentCategory,Sort,UploadDate";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId, docId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }
        /// <summary>
        /// 获取议题的地块信息
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public string GetPlatesBy(Guid topicId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select * from TopicPlate where TopicId='{0}' order by UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, topicId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        public string GetMeetingIdByTopicId(string topicId)
        {
            string meetingId = "";
            string sql = "SELECT [Id],[MeetingId] ,[Value] FROM [MeetingTopic] where Id='" + topicId;
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds != null && ds.Tables[0].Rows.Count == 1)
            {
                meetingId = ds.Tables[0].Rows[0]["MeetingId"].ToString();

            }
            return meetingId;

        }

        ///// <summary>
        ///// 判断当前用户是否已经对所有会议议题 进行表决
        ///// </summary>
        ///// <param name="meetingId"></param>
        ///// <param name="topicId"></param>
        ///// <returns></returns>
        //public string CheckTopicVote(int employeeId, int meetingId)
        //{
        //    JsonResponse response = new JsonResponse();
        //    string sql = "Select tr.TopicId,t.Id From MeetingTopic as t left outer join dbo.MeetingTopicResult as tr on t.Id = tr.TopicId where (tr.EmployeeId = " + employeeId + " or tr.EmployeeId is null) and t.MeetingId = " + meetingId;
        //    try
        //    {
        //        DataSet ds = DbHelperSQL.Query(sql);
        //        if (ds == null)
        //        {
        //            response.success = false;
        //            response.Message = "检索议题表决结果出错";
        //        }
        //        else
        //        {
        //            response.success = true;
        //            foreach (DataRow dr in ds.Tables[0].Rows)
        //            {
        //                if (string.IsNullOrEmpty(dr["TopicId"].ToString()))
        //                {
        //                    response.success = false;
        //                }
        //            }
        //            response.Message = "";
        //        }

        //        return JsonConvert.SerializeObject(response);
        //    }
        //    catch (Exception ex)
        //    {
        //        errorString = ex.Message;
        //        response.success = false;
        //        response.Message = errorString;
        //        return JsonConvert.SerializeObject(response); ;
        //    }
        //}

        ///// <summary>
        ///// 插入一条对会议议题的表决结果
        ///// </summary>
        ///// <returns></returns>
        //public string InsertTopicResult(string model)
        //{
        //    JsonResponse response = new JsonResponse();
        //    Model.MeetingTopicResult mo = JsonConvert.DeserializeObject<Model.MeetingTopicResult>(model);
        //    if (mo == null)
        //    {
        //        response.success = false;
        //        response.Message = "数据序列化出错！";
        //        return JsonConvert.SerializeObject(response);
        //    }
        //    string sql = "INSERT INTO [dbo].[MeetingTopicResult]([TopicId],[EmployeeId],[IsPass],[Comment])VALUES({0},{1},'{2}','{3}')";

        //    try
        //    {
        //        int obj = DbHelperSQL.ExecuteSql(string.Format(sql, mo.TopicId, mo.EmployeeId, mo.IsPass, mo.Comment));
        //        if (obj == 0)
        //        {
        //            response.success = false;
        //            response.Message = DbHelperSQL.errorString;
        //        }
        //        else
        //        {
        //            response.success = true;
        //            response.Message = "更新成功";
        //        }

        //        return JsonConvert.SerializeObject(response);
        //    }
        //    catch (Exception ex)
        //    {
        //        errorString = ex.Message;
        //        response.success = false;
        //        response.Message = errorString;
        //        return JsonConvert.SerializeObject(response);
        //    }
        //}

        ///// <summary>
        ///// 验证用户登录
        ///// </summary>
        ///// <param name="name"></param>
        ///// <param name="password"></param>
        ///// <returns></returns>
        //public string Login(string name, string password)
        //{
        //    JsonResponse response = new JsonResponse();
        //    string sql = "Select * from Employee where UserName = '" + name.Trim().ToLower() + "' and Password  = '" + password.Trim().ToLower() + "'";
        //    try
        //    {
        //        DataSet ds = DbHelperSQL.Query(sql);
        //        if (ds == null)
        //        {
        //            response.success = false;
        //            response.Message = DbHelperSQL.errorString;
        //        }
        //        else if (ds.Tables[0].Rows.Count == 0)
        //        {
        //            response.success = false;
        //            response.Message = "用户名或密码错误";
        //        }
        //        else
        //        {
        //            response.success = true;
        //            Meeting.Model.Employee model = new Meeting.Model.Employee();

        //            if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
        //            {
        //                model.Id = ds.Tables[0].Rows[0]["Id"].ToString();
        //            }
        //            if (ds.Tables[0].Rows[0]["DepartmentId"] != null && ds.Tables[0].Rows[0]["DepartmentId"].ToString() != "")
        //            {
        //                model.DepartmentId = int.Parse(ds.Tables[0].Rows[0]["DepartmentId"].ToString());
        //            }
        //            if (ds.Tables[0].Rows[0]["Sort"] != null && ds.Tables[0].Rows[0]["Sort"].ToString() != "")
        //            {
        //                model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
        //            }
        //            if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
        //            {
        //                model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
        //            }
        //            if (ds.Tables[0].Rows[0]["Duty"] != null && ds.Tables[0].Rows[0]["Duty"].ToString() != "")
        //            {
        //                model.Duty = ds.Tables[0].Rows[0]["Duty"].ToString();
        //            }
        //            if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
        //            {
        //                model.RoleId = int.Parse(ds.Tables[0].Rows[0]["RoleId"].ToString());
        //            }
        //            if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
        //            {
        //                model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
        //            }
        //            if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
        //            {
        //                if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
        //                {
        //                    model.IsActive = true;
        //                }
        //                else
        //                {
        //                    model.IsActive = false;
        //                }
        //            }
        //            if (ds.Tables[0].Rows[0]["UserName"] != null && ds.Tables[0].Rows[0]["UserName"].ToString() != "")
        //            {
        //                model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
        //            }
        //            if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
        //            {
        //                model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
        //            }
        //            response.ResponseData = model;
        //        }

        //        return JsonConvert.SerializeObject(response);
        //    }
        //    catch (Exception ex)
        //    {
        //        errorString = ex.Message;
        //        response.success = false;
        //        response.Message = errorString;
        //        return JsonConvert.SerializeObject(response); ;
        //    }
        //}

        /// <summary>
        /// 添加附注
        /// </summary>
        /// <param name="model"></param>
        public string AddComment(Model.MeetingComment model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into MeetingComment(");
                strSql.Append("Id,Name,MeetingId,UploadDate,EmployeeId,EmployeeName,EmployeeTitle,RoleId,PageCount,Path,URL,ObjectId)");
                strSql.Append(" values (");
                strSql.Append("@Id,@Name,@MeetingId,@UploadDate,@EmployeeId,@EmployeeName,@EmployeeTitle,@RoleId,@PageCount,@Path,@URL,@ObjectId)");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@Name", SqlDbType.NVarChar,128),
					new SqlParameter("@MeetingId", SqlDbType.Int,4),
					new SqlParameter("@UploadDate", SqlDbType.DateTime),
					new SqlParameter("@EmployeeId", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeName", SqlDbType.NVarChar,64),
					new SqlParameter("@EmployeeTitle", SqlDbType.NVarChar,64),
					new SqlParameter("@RoleId", SqlDbType.NVarChar,1024),
					new SqlParameter("@PageCount", SqlDbType.Int,4),
                    new SqlParameter("@Path", SqlDbType.NVarChar,128),
                    new SqlParameter("@URL", SqlDbType.Text),
                    new SqlParameter("@ObjectId", SqlDbType.UniqueIdentifier,16)};
                parameters[0].Value = model.Id;
                parameters[1].Value = model.Name;
                parameters[2].Value = model.MeetingId;
                parameters[3].Value = model.UploadDate;
                parameters[4].Value = model.EmployeeId;
                parameters[5].Value = model.EmployeeName;
                parameters[6].Value = model.EmployeeTitle;
                parameters[7].Value = model.RoleId;
                parameters[8].Value = model.PageCount;
                parameters[9].Value = model.Path;
                parameters[10].Value = model.URL;
                parameters[11].Value = model.ObjectId;
                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    response.success = true;
                    response.ResponseData = model;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = ex.Message;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public string Update(Meeting.Model.MeetingComment model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update MeetingComment set ");
                strSql.Append("Name=@Name,");
                strSql.Append("PageCount=@PageCount");
                strSql.Append(" where Id=@Id ");
                SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.NVarChar,128),
					new SqlParameter("@PageCount", SqlDbType.Int,4),
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
                parameters[0].Value = model.Name;
                parameters[1].Value = model.PageCount;
                parameters[2].Value = model.Id;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    response.success = true;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = ex.Message;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取用户添加的附注
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetComment(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {

                string sql = "select mc.*,m.Name as MeetingName FROM MeetingComment mc inner join Meeting m on mc.MeetingId = m.Id where mc.EmployeeId = '{0}' order by mc.UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取用户对文档所做的批注
        /// </summary>
        /// <param name="employeeId"></param>
        /// <param name="docId"></param>
        /// <returns></returns>
        public string GetCommentByDoc(string employeeId, Guid docId)
        {
            JsonResponse response = new JsonResponse();
            try
            {

                string sql = "select * FROM MeetingComment where EmployeeId = '{0}' and ObjectId = '{1}'";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId,docId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        
        }
        /// <summary>
        /// 删除一条批注
        /// </summary>
        /// <param name="commentId"></param>
        /// <returns></returns>
        public string DeleteComment(Guid commentId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from MeetingComment ");
                strSql.Append(" where Id=@Id ");
                SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
                parameters[0].Value = commentId;

                int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取用户添加的附注
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetCommentByMeeting(string employeeId, int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select mc.*,m.Name as MeetingName FROM MeetingComment mc inner join Meeting m on mc.MeetingId = m.Id where mc.EmployeeId = '{0}' and mc.MeetingId = {1} order by mc.UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取用户添加的附注
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetCommentByHistory(string employeeId, int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "select mc.*,m.Name as MeetingName FROM MeetingComment mc inner join Meeting m on mc.MeetingId = m.Id where mc.EmployeeId = '{0}' and mc.MeetingId <> {1} order by mc.UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }
                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                var obj = JsonConvert.SerializeObject(response, timeConverter);
                return obj;

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取参会人员
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetMeetingEmployees(int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT [EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId],[LoginDate] FROM [MeetingEmployee] where MeetingId = {0} Order by Sort";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取参会人员中已到人员
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetMeetingSignEmployees(int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT [EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId],[LoginDate] FROM [MeetingEmployee] where MeetingId = {0} and LoginDate is not null Order by Sort";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取数据库中内置会议类型
        /// </summary>
        /// <returns></returns>
        public string GetMeetingCategory()
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT * FROM [MeetingCategory]";
                DataSet ds = DbHelperSQL.Query(sql);
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取会议纪要
        /// </summary>
        /// <param name="meetingId"></param>
        /// <returns></returns>
        public string GetMeetingSummaries(int meetingId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "SELECT * FROM [MeetingSummary] where MeetingId = {0} order by UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, meetingId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        ///// <summary>
        ///// 插入一条会议纪要,并向参会人员征求意见
        ///// </summary>
        ///// <param name="model"></param>
        ///// <returns></returns>
        //public string AddMeetingSummary(MeetingSummary model)
        //{
        //    JsonResponse response = new JsonResponse();
        //    try
        //    {
        //        //根据会议ID获取参会人员
        //        DataSet ds = DbHelperSQL.Query(string.Format("select EmployeeId,EmployeeName,EmployeeTitle,RoleId from MeetingEmployee where MeetingId = {0}", model.MeetingId));
        //        if (ds == null)
        //        {
        //            response.success = false;
        //            response.Message = DbHelperSQL.errorString;
        //        }
        //        else
        //        {
        //            List<string> sqlList = new List<string>();
        //            //插入会议纪要记录
        //            sqlList.Add(string.Format("insert into MeetingSummary(Id,MeetingId,Path,Name,Format,ImageCount,EmployeeId,EmployeeName,EmployeeTitle,RoleId)values('{0}',{1},'{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}')",
        //                model.Id, model.MeetingId, model.Path, model.Name, model.Format, model.ImageCount, model.EmployeeId, model.EmployeeName, model.EmployeeTitle, model.RoleId));
        //            //向参会人员发送征求意见请求
        //            int rowsCount = ds.Tables[0].Rows.Count;
        //            if (rowsCount > 0)
        //            {
        //                for (int n = 0; n < rowsCount; n++)
        //                {
        //                    sqlList.Add(string.Format("INSERT INTO SummaryApprove([SummaryId],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId])VALUES('{0}','{1}','{2}','{3}','{4}')",
        //                        model.Id,
        //                        ds.Tables[0].Rows[n]["EmployeeId"].ToString(),
        //                        ds.Tables[0].Rows[n]["EmployeeName"].ToString(),
        //                        ds.Tables[0].Rows[n]["EmployeeTitle"].ToString(),
        //                        ds.Tables[0].Rows[n]["RoleId"].ToString()));
        //                }
        //            }

        //            //插入会议纪要
        //            int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
        //            //
        //            if (rows == 0)
        //            {
        //                response.success = false;
        //                response.Message = DbHelperSQL.errorString;
        //            }
        //            else
        //            {
        //                response.success = true;
        //                response.ResponseData = model;
        //            }
        //        }
        //        return JsonConvert.SerializeObject(response);
        //    }
        //    catch (Exception ex)
        //    {
        //        errorString = ex.Message;
        //        response.success = false;
        //        response.Message = ex.Message;
        //        return JsonConvert.SerializeObject(response);
        //    }
        //}

        /// <summary>
        /// 插入一条会议纪要,并向参会人员征求意见
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string AddMeetingSummary(MeetingSummary model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //插入会议纪要
                int rows = DbHelperSQL.ExecuteSql(string.Format("insert into MeetingSummary(Id,MeetingId,Path,Name,Format,ImageCount,EmployeeId,EmployeeName,EmployeeTitle,RoleId)values('{0}',{1},'{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}')",
                    model.Id, model.MeetingId, model.Path, model.Name, model.Format, model.ImageCount, model.EmployeeId, model.EmployeeName, model.EmployeeTitle, model.RoleId));
                //
                if (rows == 0)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = model;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = ex.Message;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取一条会议纪要的详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public MeetingSummary GetSummary(Guid id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from MeetingSummary ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = id;

            Meeting.Model.MeetingSummary model = new Meeting.Model.MeetingSummary();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["MeetingId"] != null && ds.Tables[0].Rows[0]["MeetingId"].ToString() != "")
                {
                    model.MeetingId = Convert.ToInt32(ds.Tables[0].Rows[0]["MeetingId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Sort"] != null && ds.Tables[0].Rows[0]["Sort"].ToString() != "")
                {
                    model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Path"] != null && ds.Tables[0].Rows[0]["Path"].ToString() != "")
                {
                    model.Path = ds.Tables[0].Rows[0]["Path"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Format"] != null && ds.Tables[0].Rows[0]["Format"].ToString() != "")
                {
                    model.Format = ds.Tables[0].Rows[0]["Format"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UploadDate"] != null && ds.Tables[0].Rows[0]["UploadDate"].ToString() != "")
                {
                    model.UploadDate = DateTime.Parse(ds.Tables[0].Rows[0]["UploadDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ImageCount"] != null && ds.Tables[0].Rows[0]["ImageCount"].ToString() != "")
                {
                    model.ImageCount = int.Parse(ds.Tables[0].Rows[0]["ImageCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                {
                    model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                    {
                        model.IsActive = true;
                    }
                    else
                    {
                        model.IsActive = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["EmployeeId"] != null && ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                {
                    model.EmployeeId = ds.Tables[0].Rows[0]["EmployeeId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["EmployeeName"] != null && ds.Tables[0].Rows[0]["EmployeeName"].ToString() != "")
                {
                    model.EmployeeName = ds.Tables[0].Rows[0]["EmployeeName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["EmployeeTitle"] != null && ds.Tables[0].Rows[0]["EmployeeTitle"].ToString() != "")
                {
                    model.EmployeeTitle = ds.Tables[0].Rows[0]["EmployeeTitle"].ToString();
                }
                if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                {
                    model.RoleId = ds.Tables[0].Rows[0]["RoleId"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsSubmit"] != null && ds.Tables[0].Rows[0]["IsSubmit"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsSubmit"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsSubmit"].ToString().ToLower() == "true"))
                    {
                        model.IsSubmit = true;
                    }
                    else
                    {
                        model.IsSubmit = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["Description"] != null && ds.Tables[0].Rows[0]["Description"].ToString() != "")
                {
                    model.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Agent"] != null && ds.Tables[0].Rows[0]["Agent"].ToString() != "")
                {
                    model.Agent = ds.Tables[0].Rows[0]["Agent"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ExtNumber"] != null && ds.Tables[0].Rows[0]["ExtNumber"].ToString() != "")
                {
                    model.ExtNumber = ds.Tables[0].Rows[0]["ExtNumber"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取会议纪要详细信息包括纪要审批意见
        /// </summary>
        /// <param name="summaryId"></param>
        /// <returns></returns>
        public string GetSummaryBy(Guid summaryId)
        {

            JsonResponse response = new JsonResponse();
            //获取会议纪要
            string sql = @"select * from MeetingSummary where Id = '{0}'
                           select * from SummaryApprove where SummaryId = '{0}'";

            try
            {
                DataSet ds = DbHelperSQL.Query(string.Format(sql, summaryId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    Model.MeetingSummary model = new Model.MeetingSummary();

                    #region 转换会议纪要基本信息
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                        {
                            model.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["MeetingId"] != null && ds.Tables[0].Rows[0]["MeetingId"].ToString() != "")
                        {
                            model.MeetingId = Convert.ToInt32(ds.Tables[0].Rows[0]["MeetingId"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Sort"] != null && ds.Tables[0].Rows[0]["Sort"].ToString() != "")
                        {
                            model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Path"] != null && ds.Tables[0].Rows[0]["Path"].ToString() != "")
                        {
                            model.Path = ds.Tables[0].Rows[0]["Path"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                        {
                            model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Format"] != null && ds.Tables[0].Rows[0]["Format"].ToString() != "")
                        {
                            model.Format = ds.Tables[0].Rows[0]["Format"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["UploadDate"] != null && ds.Tables[0].Rows[0]["UploadDate"].ToString() != "")
                        {
                            model.UploadDate = DateTime.Parse(ds.Tables[0].Rows[0]["UploadDate"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["ImageCount"] != null && ds.Tables[0].Rows[0]["ImageCount"].ToString() != "")
                        {
                            model.ImageCount = int.Parse(ds.Tables[0].Rows[0]["ImageCount"].ToString());
                        }
                        if (ds.Tables[0].Rows[0]["Comment"] != null && ds.Tables[0].Rows[0]["Comment"].ToString() != "")
                        {
                            model.Comment = ds.Tables[0].Rows[0]["Comment"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                        {
                            if ((ds.Tables[0].Rows[0]["IsActive"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsActive"].ToString().ToLower() == "true"))
                            {
                                model.IsActive = true;
                            }
                            else
                            {
                                model.IsActive = false;
                            }
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeId"] != null && ds.Tables[0].Rows[0]["EmployeeId"].ToString() != "")
                        {
                            model.EmployeeId = ds.Tables[0].Rows[0]["EmployeeId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeName"] != null && ds.Tables[0].Rows[0]["EmployeeName"].ToString() != "")
                        {
                            model.EmployeeName = ds.Tables[0].Rows[0]["EmployeeName"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["EmployeeTitle"] != null && ds.Tables[0].Rows[0]["EmployeeTitle"].ToString() != "")
                        {
                            model.EmployeeTitle = ds.Tables[0].Rows[0]["EmployeeTitle"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["RoleId"] != null && ds.Tables[0].Rows[0]["RoleId"].ToString() != "")
                        {
                            model.RoleId = ds.Tables[0].Rows[0]["RoleId"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["IsSubmit"] != null && ds.Tables[0].Rows[0]["IsSubmit"].ToString() != "")
                        {
                            if ((ds.Tables[0].Rows[0]["IsSubmit"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsSubmit"].ToString().ToLower() == "true"))
                            {
                                model.IsSubmit = true;
                            }
                            else
                            {
                                model.IsSubmit = false;
                            }
                        }
                        if (ds.Tables[0].Rows[0]["Description"] != null && ds.Tables[0].Rows[0]["Description"].ToString() != "")
                        {
                            model.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["Agent"] != null && ds.Tables[0].Rows[0]["Agent"].ToString() != "")
                        {
                            model.Agent = ds.Tables[0].Rows[0]["Agent"].ToString();
                        }
                        if (ds.Tables[0].Rows[0]["ExtNumber"] != null && ds.Tables[0].Rows[0]["ExtNumber"].ToString() != "")
                        {
                            model.ExtNumber = ds.Tables[0].Rows[0]["ExtNumber"].ToString();
                        }
                    }
                    #endregion


                    #region 会议纪要签署意见
                    int rowsCount = ds.Tables[1].Rows.Count;
                    if (rowsCount > 0)
                    {
                        Model.SummaryApprove approve;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            approve = new Model.SummaryApprove();
                            if (ds.Tables[1].Rows[n]["SummaryId"] != null && ds.Tables[1].Rows[n]["SummaryId"].ToString() != "")
                            {
                                approve.SummaryId = new Guid(ds.Tables[1].Rows[n]["SummaryId"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["EmployeeId"] != null && ds.Tables[1].Rows[n]["EmployeeId"].ToString() != "")
                            {
                                approve.EmployeeId = ds.Tables[1].Rows[n]["EmployeeId"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["EmployeeName"] != null && ds.Tables[1].Rows[n]["EmployeeName"].ToString() != "")
                            {
                                approve.EmployeeName = ds.Tables[1].Rows[n]["EmployeeName"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["EmployeeTitle"] != null && ds.Tables[1].Rows[n]["EmployeeTitle"].ToString() != "")
                            {
                                approve.EmployeeTitle = ds.Tables[1].Rows[n]["EmployeeTitle"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["RoleId"] != null && ds.Tables[1].Rows[n]["RoleId"].ToString() != "")
                            {
                                approve.RoleId = ds.Tables[1].Rows[n]["RoleId"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["IsApprove"] != null && ds.Tables[1].Rows[n]["IsApprove"].ToString() != "")
                            {
                                if ((ds.Tables[1].Rows[n]["IsApprove"].ToString() == "1") || (ds.Tables[1].Rows[n]["IsApprove"].ToString().ToLower() == "true"))
                                {
                                    approve.IsApprove = true;
                                }
                                else
                                {
                                    approve.IsApprove = false;
                                }
                            }
                            if (ds.Tables[1].Rows[n]["Comment"] != null && ds.Tables[1].Rows[n]["Comment"].ToString() != "")
                            {
                                approve.Comment = ds.Tables[1].Rows[n]["Comment"].ToString();
                            }
                            if (ds.Tables[1].Rows[n]["ApproveDate"] != null && ds.Tables[1].Rows[n]["ApproveDate"].ToString() != "")
                            {
                                approve.ApproveDate = DateTime.Parse(ds.Tables[1].Rows[n]["ApproveDate"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["InputType"] != null && ds.Tables[1].Rows[n]["InputType"].ToString() != "")
                            {
                                approve.InputType = Convert.ToInt32(ds.Tables[1].Rows[n]["InputType"].ToString());
                            }
                            if (ds.Tables[1].Rows[n]["Path"] != null && ds.Tables[1].Rows[n]["Path"].ToString() != "")
                            {
                                approve.Path = ds.Tables[1].Rows[n]["Path"].ToString();
                            }
                            model.Approves.Add(approve);
                        }
                    }
                    #endregion
                    response.ResponseData = model;
                }


                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);

            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 删除一条会议纪要
        /// </summary>
        /// <param name="commentId"></param>
        /// <returns></returns>
        public string DeleteSummary(Guid summaryId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                List<string> sqlList = new List<string>();
                sqlList.Add(string.Format("delete from MeetingSummary where Id = '{0}'", summaryId));
                sqlList.Add(string.Format("DELETE FROM SummaryApprove where SummaryId = '{0}'", summaryId));

                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 向参会人员发送会议纪要意见征求
        /// </summary>
        /// <param name="meetingId"></param>
        /// <param name="summaryId"></param>
        /// <returns></returns>
        public string RequestApprove(MeetingSummary model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                //根据会议ID获取参会人员
                DataSet ds = DbHelperSQL.Query(string.Format("select EmployeeId,EmployeeName,EmployeeTitle,Mobile,RoleId from MeetingEmployee where MeetingId = {0}", model.MeetingId));
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    List<string> sqlList = new List<string>();
                    string tels = "";//记录参会人员的手机号，如有多个，以分号隔开。
                    int rowsCount = ds.Tables[0].Rows.Count;
                    if (rowsCount > 0)
                    {
                        for (int n = 0; n < rowsCount; n++)
                        {
                            //会议纪要提交人无须征求意见
                            if (ds.Tables[0].Rows[n]["EmployeeId"].ToString() == model.EmployeeId)
                                continue;

                            sqlList.Add(string.Format("INSERT INTO SummaryApprove([SummaryId],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId],[Mobile])VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
                                model.Id,
                                ds.Tables[0].Rows[n]["EmployeeId"].ToString(),
                                ds.Tables[0].Rows[n]["EmployeeName"].ToString(),
                                ds.Tables[0].Rows[n]["EmployeeTitle"].ToString(),
                                ds.Tables[0].Rows[n]["RoleId"].ToString(),
                                ds.Tables[0].Rows[n]["Mobile"].ToString()));

                            //记录手机号
                            if (ds.Tables[0].Rows[n]["Mobile"] != null && ds.Tables[0].Rows[n]["Mobile"].ToString() != "")
                                tels += ds.Tables[0].Rows[n]["Mobile"].ToString() + ";";
                        }
                    }
                    if (sqlList.Count > 0)
                    {
                        //将会议纪要记录IsSubmit更新为true
                        sqlList.Add(string.Format("update MeetingSummary set IsSubmit=1,[Description] = '{1}',[Agent] = '{2}',[ExtNumber] = '{3}' where Id = '{0}'", model.Id, model.Description, model.Agent, model.ExtNumber));
                    }
                    //插入会议纪要审批
                    int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                    //
                    if (rows == 0)
                    {
                        response.success = false;
                        response.Message = DbHelperSQL.errorString;
                    }
                    else
                    {
                        response.success = true;
                        response.ResponseData = tels;
                    }
                }
                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 对会议纪要签署意见
        /// </summary>
        /// <param name="topicId"></param>
        /// <returns></returns>
        public string SubmitApprove(Model.SummaryApprove model)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                int obj = DbHelperSQL.ExecuteSql(string.Format("UPDATE [SummaryApprove] SET [Comment] = '{2}',[IsApprove] = 1,ApproveDate=getdate(),InputType = {3},Path='{4}' WHERE  [SummaryId] = '{0}' and [EmployeeId] = '{1}'", model.SummaryId, model.EmployeeId, model.Comment, model.InputType, model.Path));
                if (obj > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 获取相应用户的需要签署意见的会议纪要
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetUnSignSummaryBy(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = @"SELECT distinct m.Name as MeetingName,ms.[Id],ms.[Name],ms.[EmployeeName],ms.[UploadDate] 
                                FROM 
                               [MeetingSummary] ms inner join SummaryApprove sa on ms.Id = sa.SummaryId 
                               inner join Meeting m on m.Id = ms.MeetingId
                                where sa.EmployeeId = '{0}' and sa.IsApprove = 0 order by ms.UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取用户的已经签署意见的会议纪要
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetSignedSummaryBy(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = @"SELECT distinct m.Name as MeetingName,ms.[Id],ms.[Name],ms.[EmployeeName],ms.[UploadDate] 
                                FROM 
                               [MeetingSummary] ms inner join SummaryApprove sa on ms.Id = sa.SummaryId 
                               inner join Meeting m on m.Id = ms.MeetingId
                                where sa.EmployeeId = '{0}' and sa.IsApprove = 1 order by ms.UploadDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response); ;
            }
        }

        /// <summary>
        /// 获取用户参与的视频会议
        /// </summary>
        /// <param name="employeeId"></param>
        /// <returns></returns>
        public string GetMyVideoMeeting(string employeeId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = @"Select distinct m.* 
                                From VideoMeeting m inner join VideoMeetingEmployee me on m.Id = me.MeetingId 
                                where m.EmployeeId = '{0}' or me.EmployeeId = '{0}' Order By StartDate desc";
                DataSet ds = DbHelperSQL.Query(string.Format(sql, employeeId));
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    response.success = true;
                    response.ResponseData = ds.Tables[0];
                }

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm':'ss";

                return JsonConvert.SerializeObject(response, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 删除视频会议
        /// </summary>
        /// <param name="commentId"></param>
        /// <returns></returns>
        public string DeleteVideoMeeting(Guid videoId)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                List<string> sqlList = new List<string>();
                sqlList.Add(string.Format("delete from VideoMeeting where Id = '{0}'", videoId));
                sqlList.Add(string.Format("DELETE FROM VideoMeetingEmployee where MeetingId = '{0}'", videoId));

                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                    response.Message = "";
                }
                else
                {
                    response.success = false;
                    response.Message = "没有可删除的数据！";
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 过去可用的临时视频会议室
        /// </summary>
        /// <returns></returns>
        public string GetTempVideoRoom()
        {
            JsonResponse response = new JsonResponse();
            try
            {
                string sql = "Select * from VideoRoom where IsTemp = 1";
                DataSet ds = DbHelperSQL.Query(sql);
                //
                if (ds == null)
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }
                else
                {
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        response.success = false;
                        response.Message = "没有空闲的视频会议室！";
                    }
                    else
                    {
                        VideoRoom vrBll = new VideoRoom();
                        bool isExists = false;
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            if (vrBll.CheckVideoRoomUsability(dr["HostEmail"].ToString(), dr["HostPass"].ToString()))
                            {
                                isExists = true;
                                response.success = true;
                                response.ResponseData = dr;
                                break;
                            }
                        }

                        if (!isExists)
                        {
                            response.success = false;
                            response.Message = "没有空闲的视频会议室！";
                        }
                    }
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = errorString;
                return JsonConvert.SerializeObject(response);
            }
        }

        /// <summary>
        /// 保存视频会议
        /// </summary>
        /// <returns></returns>
        public string SaveVideoMeeting(VideoMeeting model, List<VideoMeetingEmployee> empList)
        {
            JsonResponse response = new JsonResponse();
            try
            {
                model.Id = Guid.NewGuid();
                List<string> sqlList = new List<string>();
                sqlList.Add(string.Format(@"INSERT INTO [VideoMeeting]([Id],[Title],[HostEmail],[HostPass],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
                    model.Id, model.Title, model.HostEmail, model.HostPass, model.EmployeeId, model.EmployeeName, model.EmployeeTitle, model.RoleId));
                empList.ForEach(x =>
                {
                    sqlList.Add(string.Format(@"INSERT INTO [VideoMeetingEmployee]([MeetingId],[EmployeeId],[EmployeeName],[EmployeeTitle],[RoleId],[Mobile])VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
                    model.Id, x.EmployeeId, x.EmployeeName, x.EmployeeTitle, x.RoleId, x.Mobile));

                });
                int rows = DbHelperSQL.ExecuteSqlTran(sqlList);
                if (rows > 0)
                {
                    response.success = true;
                }
                else
                {
                    response.success = false;
                    response.Message = DbHelperSQL.errorString;
                }

                return JsonConvert.SerializeObject(response);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                response.success = false;
                response.Message = ex.Message;
                return JsonConvert.SerializeObject(response);
            }

        }
    }
}
